-- =========================================================================
-- Title: Compare 2 days of auditing data
-- Author: Nem W. Schlecht
-- Create Date: 2013-07-01
-- License: Creative Commons Attribution-Share Alike
-- Description: Stored procedure to compare the data from 2 different days
--     of data gathered by SQL Auditng system
-- =========================================================================

USE ServerAudit;
GO

IF (
        EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'P'
                AND NAME = 'compare'
            )
        )
BEGIN
    DROP PROCEDURE [compare];
END;
GO

--
-- Database: ServerAudit
--   Schema: dbo
--     Name: compare
--
CREATE PROCEDURE [dbo].[compare] 
	@date1 VARCHAR(15) = NULL
	, @date2 VARCHAR(15) = NULL
	, @htmloutput BINARY = 0
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @histId1 INT;
	DECLARE @histId2 INT;
	DECLARE @lastRowCount INT;
	
	-- Calculate default values if no params set
	IF (@date1 IS NULL)
	BEGIN
		SELECT @histId1 = MAX(histId)
		FROM auditHistory
		;

		SELECT @date1 = histDate
		FROM auditHistory
		WHERE histId = @histId1
		;
	END
	ELSE
	BEGIN
		SELECT @histId1 = MAX(histId)
		FROM auditHistory
		WHERE CAST(@date1 AS DATE) = CAST(histDate AS DATE)
		;
	END;
	
	IF (@date2 IS NULL)
	BEGIN
		SELECT @histId2 = MAX(histId)
		FROM auditHistory
		WHERE histId < @histId1
		;

		SELECT @date2 = histDate
		FROM auditHistory
		WHERE histId = @histId2
		;
	END
	ELSE
	BEGIN
		SELECT @histId2 = MAX(histId)
		FROM auditHistory
		WHERE CAST(@date2 AS DATE) = CAST(histDate AS DATE)
		;
	END;
	
	IF (@htmloutput = 1)
	BEGIN
		PRINT '<html>
<head>
<title>Server Audit Compare Report: '
			+ CAST(@date2 AS VARCHAR(120))
			+ ' - '
			+ CAST(@date1 AS VARCHAR(120))
			+ '</title>
</head>
<body>
<table style="min-width:50%">
';
	END;

	DECLARE @SQL NVARCHAR(2000);

	--PRINT 'Change Comparison';
	-- Changed Values
	--SET @SQL = '
	--SELECT au1.ServerName
	--		, au1.ikey
	--		, au1.ivalue AS [changed_' + @date1 + ']
	--		, au2.ivalue AS [' + @date2 + ']
	--FROM ' + @date1 + ' AS au1
	--	JOIN ' + @date2 + ' AS au2
	--		ON au1.ServerName = au2.ServerName
	--			AND au1.ikey = au2.ikey
	--	LEFT JOIN auditCompareRules AS acr
	--		ON au1.ikey LIKE '''' + acr.configKey+ ''''
	--WHERE au1.ivalue <> au2.ivalue
	--	AND acr.action IS NULL
	--'

	IF (@htmloutput = 1)
	BEGIN
		SET @SQL = '
			SELECT ''<table border=1 cellpadding=3 style="min-width:100%">''
				+ ''<tr><td align="right">Instance:</td><td>''
				+ au1.ServerName
				+ ''</td></tr>''
				+ CHAR(10)
				+ ''<tr><td align="right">Variable:</td><td>''
				+ au1.ikey
				+ ''</td></tr>''
				+ CHAR(10)
				+ ''<tr><td align="right">Old value:</td><td>''
				+ ISNULL(au2.ivalue, ''(NULL)'')
				+ ''</td></tr>''
				+ CHAR(10)
				+ ''<tr><td align="right">New value:</td><td>''
				+ ISNULL(au1.ivalue, ''(NULL)'')
				+ ''</td></tr>''
				+ ''</table><br>''
				+ CHAR(10) AS ''<h1 style="text-align: center">Changed Values</h1>''
			';
	END;
	ELSE
	BEGIN
		SET @SQL = '
			SELECT '' Instance: ''
				+ au1.ServerName
				+ CHAR(10)
				+ '' Variable: ''
				+ au1.ikey
				+ CHAR(10)
				+ ''Old value: ''
				+ ISNULL(au2.ivalue, ''(NULL)'')
				+ CHAR(10)
				+ ''New value: ''
				+ ISNULL(au1.ivalue, ''(NULL)'')
				+ CHAR(10) AS ''
-- Changed Values --''
		';
	END;

	SET @SQL = @SQL + '
		FROM auditData AS au1
				JOIN auditData AS au2
					ON au1.ServerName = au2.ServerName
						AND au1.ikey = au2.ikey
				LEFT JOIN auditCompareRules AS acr
					ON au1.ikey LIKE '''' 
						+ acr.configKey
						+ ''''
		WHERE au1.ivalue <> au2.ivalue
			AND acr.action IS NULL
			AND au1.histId = '
			+ CAST(@histId1 AS VARCHAR(20))
			+ '
			AND au2.histId = '
			+ CAST(@histId2 AS VARCHAR(20))
			+ '
		ORDER BY au1.ServerName
			, au1.ikey
	';
	
	--SELECT @histId1, @histId2, @SQL;
	
	IF (@htmloutput = 1)
	BEGIN
		PRINT '<tr bgcolor="#aadddd">';
		PRINT '<td>';
	END;

	EXECUTE sp_executesql 
		@stmt = @SQL;

	SET @lastRowCount = @@ROWCOUNT;
	
	IF (@lastRowCount = 0)
	BEGIN
		PRINT '-- *** No changed values ***';
		PRINT '';
	END;
	
	IF (@htmloutput = 1)
	BEGIN
		PRINT '</td>';
		PRINT '</tr>';
	END;

	-- PRINT 'New Settings'
	---- New Values
	--SET @SQL = '
	--SELECT au1.ServerName
	--		, au1.ikey
	--		, au1.ivalue AS [new_' + @date1 + ']
	--		-- , au2.ivalue AS [' + @date2 + ']
	--FROM ' + @date1 + ' AS au1
	--	   LEFT JOIN ' + @date2 + ' AS au2
	--				ON au1.ServerName = au2.ServerName
	--						AND au1.ikey = au2.ikey
	--WHERE au2.ServerName IS NULL AND au1.ServerName IS NOT NULL
	--'

	IF (@htmloutput = 1)
	BEGIN
		SET @SQL = '
			SELECT ''<table border=1 cellpadding=3 style="min-width:100%">''
				+ ''<tr><td align="right">Instance:</td><td>''
				+ au1.ServerName
				+ ''</td></tr>''
				+ CHAR(10)
				+ ''<tr><td align="right">Variable:</td><td>''
				+ au1.ikey
				+ ''</td></tr>''
				+ CHAR(10)
				+ ''<tr><td align="right">New value:</td><td>''
				+ ISNULL(au1.ivalue, ''(NULL)'')
				+ ''</td></tr>''
				+ ''</table><br>''
				+ CHAR(10) AS ''<h1 style="text-align: center">New Values</h1>''
			';
	END;
	ELSE
	BEGIN
		SET @SQL = '
			SELECT '' Instance: ''
				+ au1.ServerName
				+ CHAR(10)
				+ '' Variable: ''
				+ au1.ikey
				+ CHAR(10)
				+ ''New value: ''
				+ ISNULL(au1.ivalue, ''(NULL)'')
				+ CHAR(10) AS ''
-- New Values --''
		';
	END;

	SET @SQL = @SQL + '
		FROM auditData AS au1
			LEFT JOIN auditData AS au2
				ON au1.ServerName = au2.ServerName
					AND au1.ikey = au2.ikey
					AND au2.histId = '
			+ CAST(@histId2 AS VARCHAR(20))
			+ '
		WHERE au2.ServerName IS NULL
			AND au1.ServerName IS NOT NULL
			AND au1.histId = '
			+ CAST(@histId1 AS VARCHAR(20))
			+ '
	';
	
	IF (@htmloutput = 1)
	BEGIN
		PRINT '<tr bgcolor="#aaddaa">';
		PRINT '<td>';
	END;

	EXECUTE sp_executesql 
		@stmt = @SQL;

	SET @lastRowCount = @@ROWCOUNT;

	IF (@lastRowCount = 0)
	BEGIN
		PRINT '-- *** No new values ***';
		PRINT '';
	END;
	
	IF (@htmloutput = 1)
	BEGIN
		PRINT '</td>';
		PRINT '</tr>';
	END;

	-- PRINT 'Deleted Settings'
	---- Deleted Values
	--SET @SQL = '
	--SELECT au2.ServerName
	--		, au2.ikey
	--		--, au1.ivalue AS [' + @date1 + ']
	--		, au2.ivalue AS [deleted_' + @date2 + ']
	--FROM ' + @date2 + ' AS au2
	--	   LEFT JOIN ' + @date1 + ' AS au1
	--				ON au1.ServerName = au2.ServerName
	--						AND au1.ikey = au2.ikey
	--WHERE au2.ServerName IS NOT NULL AND au1.Servername IS NULL
	--'

	IF (@htmloutput = 1)
	BEGIN
		SET @SQL = '
			SELECT ''<table border=1 cellpadding=3 style="min-width:100%">''
				+ ''<tr><td align="right">Instance:</td><td>''
				+ au1.ServerName
				+ ''</td></tr>''
				+ CHAR(10)
				+ ''<tr><td align="right">Variable:</td><td>''
				+ au1.ikey
				+ ''</td></tr>''
				+ CHAR(10)
				+ ''<tr><td align="right">Old value:</td><td>''
				+ ISNULL(au2.ivalue, ''(NULL)'')
				+ ''</td></tr>''
				+ CHAR(10)
				+ ''</table><br>''
				+ CHAR(10) AS ''<h1 style="text-align: center">Old Values</h1>''
			';
	END;
	ELSE
	BEGIN
		SET @SQL = '
			SELECT '' Instance: ''
				+ au2.ServerName
				+ CHAR(10)
				+ '' Variable: ''
				+ au2.ikey
				+ CHAR(10)
				+ ''Old value: ''
				+ ISNULL(au2.ivalue, ''(NULL)'')
				+ CHAR(10) AS ''
-- Old Values --''
		';
	END;
	
	SET @SQL = @SQL + '
		FROM auditData AS au2
		   LEFT JOIN auditData AS au1
				ON au1.ServerName = au2.ServerName
					AND au1.ikey = au2.ikey
					AND au1.histId = '
					+ CAST(@histId1 AS VARCHAR(20))
					+ '
		WHERE au2.ServerName IS NOT NULL
			AND au1.ServerName IS NULL
			AND au2.histId = '
			+ CAST(@histId2 AS VARCHAR(20))
			+ '
	';

	IF (@htmloutput = 1)
	BEGIN
		PRINT '<tr bgcolor="#aaaadd">';
		PRINT '<td>';
	END;

	EXECUTE sp_executesql 
		@stmt = @SQL;

	SET @lastRowCount = @@ROWCOUNT;

	IF (@lastRowCount = 0)
	BEGIN
		PRINT '-- *** No old values ***';
		PRINT '';
	END;
	
	IF (@htmloutput = 1)
	BEGIN
		PRINT '</td>';
		PRINT '</tr>';
		PRINT '</table>';
		PRINT '</body>';
		PRINT '</html>';
	END;
END;

GO

--EXEC dbo.compare;

--EXEC dbo.compare
--	@date1 = '2015-03-21'
--	, @date2 = '2015-02-28'
--	, @htmloutput = 0
--;

--GO
